package sk.stuba.fiit.repka.dp.db;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class DBExpectationsManipulator implements DataManipulator{
	private static final String INSERT_EXPECTATIONS_QUERY = "INSERT INTO EXPECTATIONS" +
			"(\"ID_TYPE\", \"ID_COMPOSER\", \"ID_APPROACH\", \"URL\", \"CREATION_DATE\" ) " +
			"VALUES (?, ?, ?, ?, ?)";
	
	private String expectations;
	private DatabaseController dbControl;
	private String typeValue;
	
	private int id; //id of currently stored expectations
	
	public int getId() {
		return id;
	}

	public DBExpectationsManipulator(DatabaseController dbControl, String expectations, String typeValue) {
		this.dbControl = dbControl;
		this.expectations = expectations;
		this.typeValue = typeValue;
	}
	
	private void setIdCurrentlyStoredExpectations() throws SQLException {
		String query = "SELECT ID FROM  EXPECTATIONS WHERE URL=?";
		PreparedStatement statement = this.dbControl.getCon().prepareStatement(query);
		statement.setString(1, this.expectations);
		ResultSet rs = statement.executeQuery();
		if(rs.next()) {
			this.id = rs.getInt("ID");
		}
		rs.close();
		statement.close();
	}
	
	private int getApproachId(String approachAcronym) throws SQLException {
		int result = -1;
		String query = "SELECT * FROM REPRESENTATIONAPPROACH WHERE ACRONYM=?";
		
		PreparedStatement statement = this.dbControl.getCon().prepareStatement(query);
		statement.setString(1, approachAcronym);
		ResultSet rs = statement.executeQuery();
		if(rs.next()) {
			result = rs.getInt("ID");
		}
		rs.close();
		statement.close();
		
		return result;
	}
	
	private int getTypeId(String typeValue) throws SQLException {
		int result = -1;
		String query = "SELECT * FROM TYPE WHERE VALUE=?";
		
		PreparedStatement statement = this.dbControl.getCon().prepareStatement(query);
		statement.setString(1, typeValue);
		ResultSet rs = statement.executeQuery();
		if(rs.next()) {
			result = rs.getInt("ID");
		}
		rs.close();
		statement.close();
		
		return result;
	}
	
	private int getComposerId(String componserName) throws SQLException {
		int result = -1;
		String query = "SELECT * FROM COMPOSER WHERE NAME=?";
		
		PreparedStatement statement = this.dbControl.getCon().prepareStatement(query);
		statement.setString(1, componserName);
		ResultSet rs = statement.executeQuery();
		if(rs.next()) {
			result = rs.getInt("ID");
		}
		rs.close();
		statement.close();
		
		return result;
	}
	
	@Override
	public boolean insert() throws SQLException {
		boolean result = false;
		int composerID = this.getComposerId(EnumUtils.OWLS_XPLAN);
		int approachID = this.getApproachId(EnumUtils.ACRONYM);
		int typeID = this.getTypeId(this.typeValue);
		
		this.dbControl.getCon().setAutoCommit(false);
		PreparedStatement statement = this.dbControl.getCon().prepareStatement(INSERT_EXPECTATIONS_QUERY);
		statement.setInt(1, typeID);
		statement.setInt(2, composerID);
		statement.setInt(3, approachID);
		statement.setString(4, this.expectations);
		java.util.Date date = new java.util.Date();
		Date sqlDate = new Date(date.getTime());
		statement.setDate(5, sqlDate);
		int count = statement.executeUpdate();
		this.dbControl.getCon().commit();
		statement.close();
		this.dbControl.getCon().setAutoCommit(true);
		
		this.setIdCurrentlyStoredExpectations();
		
		if(count > 0) {
			result = true;
		}
		return result;
	}

	@Override
	public boolean update(List<? extends Object> toUpdate) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean delete(String query) {
		// TODO Auto-generated method stub
		return false;
	}

}
